﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DLL
{
    public class DAPackage
    {
        // Chuỗi kết nối
        string strConnection;

        // SqlConnection
        SqlConnection sqlConn = null;
        private StringBuilder sbCommand = new StringBuilder(String.Empty);

        // Ten bang
        private const string c_table = "pack";

        // Ten cot cua bang Gois thau
        private const string c_id = "package_id";
        private const string c_package_name = "package_name";
        private const string c_total_investment = "total_investment";
        private const string c_dvTienTe = "dvTienTe";
        private const string c_bidding_form = "bidding_form";
        private const string c_deployment_time = "deployment_time";

        public DAPackage(string strConnection)
        {
            this.strConnection = strConnection;
        }

        private void OpenConnection()
        {
            sqlConn = new SqlConnection(strConnection);
            sqlConn.Open();
        }

        private void CloseConnection()
        {
            sqlConn.Close();
        }

        public DataSet GetPackages(string package_name)
        {
            DataSet ds = new DataSet();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendLine("SELECT  package_id");
            sbCommand.AppendLine(",package_name");
            sbCommand.AppendLine(",total_investment");
            sbCommand.AppendLine(",dvTienTe");
            sbCommand.AppendLine(",bidding_form");
            sbCommand.AppendLine(",deployment_time");
            sbCommand.AppendFormat("FROM {0} PK", c_table).AppendLine();

            if (!string.IsNullOrEmpty(package_name))
            {
                sbCommand.AppendFormat("WHERE PK.package_name LIKE N'%{0}%'", package_name).AppendLine();
            }

            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            this.CloseConnection();
            return ds;
        }

        private bool IsExistPackage(BLL.BOPackage pack, string strConnection)
        {
            sbCommand = new StringBuilder(String.Empty);
            string package_name = pack.Package_name.ToString();
            sbCommand.AppendFormat("SELECT * FROM {0} WHERE {1} = @package_name", c_table, c_package_name).AppendLine();
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            SqlDataAdapter da = new SqlDataAdapter(sbCommand.ToString(), sqlConnection);
            da.SelectCommand.Parameters.AddWithValue("@package_name", package_name);
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);

            if (dataTable.Rows.Count < 1)
            {
                // Gói thầu chua ton tai
                return false;
            }
            else
            {
                // Gói thầu da ton tai
                return true;
            }
        }


        private string BuildInsertString(BLL.BOPackage pack)
        {
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("INSERT INTO {0}", c_table).AppendLine();
            sbCommand.AppendFormat("({0},", c_package_name).AppendLine();
            sbCommand.AppendFormat("{0},", c_total_investment).AppendLine();
            sbCommand.AppendFormat("{0},", c_dvTienTe).AppendLine();
            sbCommand.AppendFormat("{0},", c_bidding_form).AppendLine();
            sbCommand.AppendFormat("{0})", c_deployment_time).AppendLine();
            sbCommand.AppendFormat("VALUES(N'{0}', N'{1}', N'{2}', N'{3}', N'{4}')", pack.Package_name, pack.Total_investment, pack.DvTienTe, pack.Bidding_form, pack.Deployment_time).AppendLine();

            return sbCommand.ToString();
        }

        private string BuildUpdateString(BLL.BOPackage pack)
        {
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("UPDATE {0}", c_table).AppendLine();
            sbCommand.AppendFormat("SET {0} = N'{1}',", c_package_name, pack.Package_name).AppendLine();
            sbCommand.AppendFormat("{0} =  N'{1}',", c_total_investment, pack.Total_investment).AppendLine();
            sbCommand.AppendFormat("{0} =  N'{1}',", c_dvTienTe, pack.DvTienTe).AppendLine();
            sbCommand.AppendFormat("{0} =  N'{1}',", c_deployment_time, pack.Deployment_time).AppendLine();
            sbCommand.AppendFormat("{0} =  N'{1}'", c_bidding_form, pack.Bidding_form).AppendLine();
            sbCommand.AppendFormat("WHERE {0} =  N'{1}'", c_id, pack.Package_id).AppendLine();

            return sbCommand.ToString();
        }

        public bool CreatePackage(BLL.BOPackage pack, string strConnection)
        {

            string strINSERT = this.BuildInsertString(pack);

            // Kiem tra chu dau tu da ton tai trong co so du lieu chua
            if (IsExistPackage(pack, strConnection))
            {
                return false;
            }
            else
            {
                SqlConnection sqlConnection = new SqlConnection(strConnection);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strINSERT, sqlConnection);

                // execute Query
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                return true;
            }
        }

        public int GetMaxPackageID()
        {
            int maxID = 0;
            DataTable dt = new DataTable();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("SELECT  Max(package_id) maxid FROM {0} pack", c_table).AppendLine();
            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            if (dt.Rows.Count == 1)
            {
                DataRow DR;
                DR = dt.Rows[0];
                maxID = Convert.ToInt32(DR["maxid"]);
            }
            return maxID;
        }

        public int GetPackageID(string strPackageName)
        {
            DataTable dt = new DataTable();
            int package_id = 0;
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("SELECT  package_id FROM {0} PK", c_table).AppendLine();
            sbCommand.AppendFormat("WHERE PK.package_name =N'{0}'", strPackageName).AppendLine();
            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            if (dt.Rows.Count >0)
            {
                DataRow DR;
                DR = dt.Rows[0];
                package_id = Convert.ToInt32(DR["package_id"]);
            }
            else
            {
                package_id = this.GetMaxPackageID() + 1;
            }

            this.CloseConnection();

            return package_id;
        }

        public void UpdatePackage(BLL.BOPackage boPack, string strConnection)
        {

            string strUpdate = this.BuildUpdateString(boPack);

            try
            {
                SqlConnection sqlConnection = new SqlConnection(strConnection);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strUpdate, sqlConnection);

                // execute Query
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
            }
        }
    }
}
